﻿using System;
using System.Collections.Generic;
using System.Linq;
using UcAsp.Net.Model;

namespace UcAsp.Net.MSSQLDAL
{
    public class ArticlesDAL : UcAsp.Net.IDAL.IArticlesDAL
    {
        public List<Articles> Select(int? userid, int? categoriesid, bool isrecommend, bool isrelease, DateTime? begin, DateTime? end, string keyword, int pagesize, int pageindex, out int pagecount)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                List<Articles> list = new List<Articles>();
                IQueryable<Articles> query = entities.Articless.OrderByDescending(o => o.Rank);
                if (userid != null)
                {
                    query = query.Where(o => o.UserID == userid);
                }
                if (categoriesid != null)
                {
                    query = query.Where(o => o.CategorieID == categoriesid);
                }
                if (isrecommend)
                {
                    query = query.Where(o => o.IsRecommend == true);
                }
                if (isrelease)
                {
                    query = query.Where(o => o.IsRelease == true);
                }

                if (begin != null)
                {
                    query = query.Where(o => o.AddDatetime > begin);
                }
                if (end != null)
                {
                    query = query.Where(o => o.AddDatetime < end);
                }
                if (keyword != null)
                {
                    query = query.Where(o => o.Tag.Contains(keyword) || o.Title.Contains(keyword) || o.MetaKeyword.Contains(keyword) || o.MetaDescrition.Contains(keyword));
                }
                pagecount = (query.Count() / pagesize) + 1;
                list = query.OrderByDescending(o => o.Rank).ThenByDescending(o => o.ID).Skip(pagesize * pageindex).Take(pagesize).ToList();
                return list;
            }
        }

        public List<ArticelsViews> SelectViews(int? userid, int? categoriesid, bool? isrecommend, bool? isrelease, DateTime? begin, DateTime? end, string keyword, int pagesize, int pageindex, out int pagecount)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                List<ArticelsViews> list = new List<ArticelsViews>();
                IQueryable<ArticelsViews> query = entities.ArticelsViewss.OrderByDescending(o=>o.Rank);
                if (categoriesid != null)
                {
                    query = query.Where(o => o.CategorieID == categoriesid);
                }
                if (userid != null)
                {
                    query = query.Where(o => o.UserID == userid);
                }
                if (isrecommend != null)
                {
                    query = query.Where(o => o.IsRecommend == isrecommend);
                }
                if (isrelease != null)
                {
                    query = query.Where(o => o.IsRelease == isrelease);
                }
                if (begin != null)
                {
                    query = query.Where(o => o.AddDatetime > begin);
                }

                if (end != null)
                {
                    query = query.Where(o => o.AddDatetime < end);
                }
                if (keyword != null)
                {
                    query = query.Where(o => o.Tag.Contains(keyword) || o.Title.Contains(keyword) || o.MetaKeyword.Contains(keyword) || o.MetaDescrition.Contains(keyword));
                }

                pagecount = (query.Count() / pagesize) + 1;
                list = query.OrderByDescending(o => o.Rank).ThenByDescending(o => o.ID).Skip(pagesize * pageindex).Take(pagesize).ToList();
                return list;
            }
        }

        public bool Insert(Articles model)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                model.AddDatetime = DateTime.Now;
                DateTime datetime = Convert.ToDateTime("1970-01-01");

                model.IconUrl = model.IconUrl == null ? "" : model.IconUrl;
                model.Note1 = model.Note1 == null ? "" : model.Note1;
                model.Note2 = model.Note2 == null ? "" : model.Note2;
                model.Note3 = model.Note3 == null ? "" : model.Note3;
                model.Note4 = model.Note4 == null ? "" : model.Note4;
                model.Note5 = model.Note5 == null ? "" : model.Note5;
                model.Note6 = model.Note6 == null ? "" : model.Note6;
                model.Note7 = model.Note7 == null ? "" : model.Note7;
                model.Note8 = model.Note8 == null ? "" : model.Note8;
                model.Note9 = model.Note9 == null ? "" : model.Note9;

                model.Date1 = model.Date1 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date1;
                model.Date2 = model.Date2 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date2;
                model.Date3 = model.Date3 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date3;
                model.Date4 = model.Date4 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date4;
                model.Date5 = model.Date5 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date5;
                model.Date6 = model.Date6 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date6;
                model.Date7 = model.Date7 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date7;
                model.Date8 = model.Date8 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date8;

                entities.Articless.Add(model);
                entities.SaveChanges();
                return true;
            }
        }

        public bool Update(Articles model)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                DateTime datetime = Convert.ToDateTime("1970-01-01");
                System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                entry.State = System.Data.EntityState.Modified;
                model.Date1 = model.Date1 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date1;
                model.Date2 = model.Date2 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date2;
                model.Date3 = model.Date3 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date3;
                model.Date4 = model.Date4 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date4;
                model.Date5 = model.Date5 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date5;
                model.Date6 = model.Date6 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date6;
                model.Date7 = model.Date7 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date7;
                model.Date8 = model.Date8 == DateTime.Parse("0001-01-01 00:00:00") ? datetime : model.Date8;

                entities.SaveChanges();
                return true;
            }
        }

        public bool Delete(Articles model)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                if (model != null)
                {
                    System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                    entry.State = System.Data.EntityState.Deleted;
                    entities.SaveChanges();
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool Delete(DateTime Time, int CategoriesID)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                entities.Database.ExecuteSqlCommand("delete from Articles where AddDatetime<'" + Time + "' and CategorieID='" + CategoriesID + "'");
                return true;
            }
        }

        public Articles Limt(int id)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Model.Articles model = entities.Articless.FirstOrDefault(o => o.ID == id);
                model.Date1 = DateTime.Now;
                model.Hot++;
                System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                entry.State = System.Data.EntityState.Modified;
                entities.SaveChanges();
                return entities.Articless.FirstOrDefault(o => o.ID == id);
            }
        }

        public Articles GetArtieByTitle(string title)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                return entities.Articless.FirstOrDefault(o => o.Title == title);
            }
        }

        public ArticelsViews GetArtieByShowPath(string showpath, out int count, out List<ArticelsViews> nextorprev)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Model.Articles model = entities.Articless.FirstOrDefault(o => o.ShowPath == showpath);
                model.Date1 = DateTime.Now;
                model.Hot++;
                System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                entry.State = System.Data.EntityState.Modified;
                entities.SaveChanges();
                List<ArticelsViews> list = new List<ArticelsViews>();
                Model.ArticelsViews nextmodel = entities.ArticelsViewss.OrderByDescending(o => o.ID).Where(o => o.CategorieID == model.CategorieID).FirstOrDefault(o => o.ID < model.ID);
                list.Add(nextmodel);
                Model.ArticelsViews prevmodel = entities.ArticelsViewss.OrderBy(o => o.ID).Where(o => o.CategorieID == model.CategorieID).FirstOrDefault(o => o.ID > model.ID);
                list.Add(prevmodel);
                nextorprev = list;
                count = entities.ArticlesPages.Where(o => o.ArticlesID == model.ID).Count();
                return entities.ArticelsViewss.FirstOrDefault(o => o.ShowPath == showpath);
            }
        }

        public ArticelsViews GetArtieById(int id, out int count, out List<Articles> nextorprev)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Model.Articles model = entities.Articless.FirstOrDefault(o => o.ID == id);
                if (model != null)
                {
                    model.Date1 = DateTime.Now;
                    model.Hot++;
                    System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                    entry.State = System.Data.EntityState.Modified;
                    entities.SaveChanges();
                }
                count = entities.ArticlesPages.Where(o => o.ArticlesID == id).Count();
                if (model != null)
                {
                    model.Date1 = DateTime.Now;
                    model.Hot++;
                    System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                    entry.State = System.Data.EntityState.Modified;
                    entities.SaveChanges();
                }
                List<Articles> list = new List<Articles>();
                Model.Articles nextmodel = entities.Articless.OrderByDescending(o => o.ID).Where(o => o.CategorieID == model.CategorieID).FirstOrDefault(o => o.ID < model.ID);
                list.Add(nextmodel);
                Model.Articles prevmodel = entities.Articless.OrderBy(o => o.ID).Where(o => o.CategorieID == model.CategorieID).FirstOrDefault(o => o.ID > model.ID);
                list.Add(prevmodel);
                nextorprev = list;
                count = entities.ArticlesPages.Where(o => o.ArticlesID == id).Count();
                return entities.ArticelsViewss.FirstOrDefault(o => o.ID == id);
            }
        }

        public Articles RecommdRecord(int id, bool recommd)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Model.Articles model = entities.Articless.FirstOrDefault(o => o.ID == id);
                model.IsRecommend = recommd;
                System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                entry.State = System.Data.EntityState.Modified;
                entities.SaveChanges();
                return model;
            }
        }

        public Articles ReleaseRecord(int id, bool release)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                Model.Articles model = Limt(id);
                model.IsRelease = release;
                System.Data.Entity.Infrastructure.DbEntityEntry<Articles> entry = entities.Entry<Articles>(model);
                entry.State = System.Data.EntityState.Modified;
                entities.SaveChanges();
                return model;
            }
        }

        public List<ArticelsViews> Subject(string categoriesid, string keyword, int maxnum, int whoview)
        {
            using (UcAspNetEntities entities = new UcAspNetEntities())
            {
                string[] result = categoriesid.Split(',');
                int[] catid = new int[result.Length];
                for (int i = 0; i < result.Length; i++)
                {
                    catid[i] = int.Parse(result[i]);
                }
                List<ArticelsViews> list = new List<ArticelsViews>();
                IQueryable<ArticelsViews> query = entities.ArticelsViewss;
                query = from p in query
                        where (catid).Contains(p.CategorieID) && (p.Title.Contains(keyword) || p.Tag.Contains(keyword)) && p.IsRelease == true
                        select p;
                switch (whoview)
                {
                    case 1:
                        query = query.OrderByDescending(o => o.Date1);
                        break;

                    case 2:
                        query = query.OrderByDescending(o => o.AddDatetime);
                        break;

                    case 3:
                        query = query.Where(o => o.IsRecommend == true);
                        break;

                    case 4:
                        query = query.OrderByDescending(o => o.Hot);
                        break;

                    case 5:
                        query = query.Where(o => !string.IsNullOrEmpty(o.IconUrl));
                        break;

                    case 6:
                        query = query.Where(o => !string.IsNullOrEmpty(o.IconUrl) && o.IsRecommend == true);
                        break;

                    default:
                        break;
                }
                query = query.Take(maxnum);
                return query.ToList();
            }
        }
    }
}